﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Convert.DataAccess;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using System.Configuration;
using System.Data.OleDb;
using System.Windows;
namespace Convert.DBconverter
{
    class ManufsConverter
    {
        private static int BATCH_SAVE_SIZE = 5000;
        public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
        public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;

        public static void ConvertManufs(string lotsConnectionString, string cpiamConnectionString)
        {
            LotsConnectionString = lotsConnectionString;
            CpiamConnectionString = cpiamConnectionString;
            PopulateManfCodeMap();
            DataTable ManufacturersDT = DataTables.CreateManufacturersDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT ManufacturerCode, ManufacturerName FROM Manufacturer");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                            Manufacturer manufacturer = new Manufacturer();
                            int i = 0;
                            manufacturer.ManufacturerCode= reader.IsDBNull(i) ? null : reader.GetString(i);
                            manufacturer.ManufacturerName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            if (!ManfCodeMap.ContainsKey(manufacturer.ManufacturerCode))
                            {
                                SaveManufacturer(manufacturer, ManufacturersDT);
                            }
                        if (ManufacturersDT.Rows.Count % BATCH_SAVE_SIZE == 0)
                        {
                            SaveAllDataTables(CpiamConnectionString, ManufacturersDT);
                        }
                    }
                    SaveAllDataTables(CpiamConnectionString, ManufacturersDT);
                }
            }
            catch (Exception ex)
            {
                Logger.LogWarningAndError("Error in Convert Manufs ", ex);
                MessageBox.Show("Error converting Agencies: " + ex.Message + "\n\nStackTrace=" + ex.StackTrace);
            }
            finally
            {
                dbConn.Close();
            }
        }

        private static void SaveAllDataTables(String ConnectionString, DataTable ManufacturersDT)
        {
            if (ManufacturersDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(ManufacturersDT, "Manufacturers", ConnectionString);
        }

        private static void SaveManufacturer(Manufacturer manufacturer, DataTable ManufacturersDT)
        {
            Manufacturers manufacturers = new Manufacturers(manufacturer.ManufacturerCode.ToUpper(), manufacturer.ManufacturerName.ToUpper());
            Manufacturers.SaveManufacturersToDataTable(manufacturers, ManufacturersDT);
            //ManufacturersDT.Rows.Add(Guid.NewGuid(), manufacturer.ManufacturerCode.ToUpper(), manufacturer.ManufacturerName.ToUpper());
        }

        public static Dictionary<string, string> ManfCodeMap;
        public static void PopulateManfCodeMap()
        {
            ManfCodeMap = new Dictionary<string, string>();
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"Select ManfCode, ManfName from Manufacturers order by ManfCode");
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    string ManfCode = reader.GetString(0);
                    string ManfName = reader.GetString(1);
                    ManfCodeMap[ManfCode] = ManfName;
                }
                reader.Close();
            }
            dbConn.Close();
        }
    }
}
